﻿/*----------------------------------------------------------------
// Copyright (C) 2011 上海
// 版权所有。 
//
// 文件名：DataAccessOneCanteenSummaryReportDetail
// 文件功能描述：一食堂详细汇总报表数据访问层
//
// 
// 创建标识：2012-05-22 王冲
//
// 修改标识：
// 修改描述：
//
//
//----------------------------------------------------------------*/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DbAdapter.sqldbAdapter;
using System.Data;
using System.Data.SqlClient;

namespace DAO.WaiGaoQiao
{
    public class DataAccessOneCanteenSummaryReportDetail
    {
        /// <summary>
        /// 获取一食堂汇总表
        /// </summary>
        /// <returns></returns>
        public DataSet BindList(int pagesize, int startindex, string strWhere)
        {
            sqldb db = new sqldb();
            string query = string.Empty;
            int startrow = pagesize * (startindex - 1);
            int endrow = pagesize * startindex + 1;
            query = "select * from (select ROW_NUMBER() OVER(ORDER BY e.MenuId ASC) rownumber,e.MenuId,MenuName,COUNT(e.MenuName) as Counts,SUM(d.Price) as Price " +
                           "from Con_Record a inner join YAX_MACHINE b on a.MacID=b.MacID inner join Yax_Node c on b.NodeID=c.NodeID " +
                           "inner join CON_RECORD_DETAIL d on a.ID=d.Record_ID left join Con_Menus e on d.MenuId=e.MenuId "+
                           //"inner join FILE_PERSONNEL_VIEW f on a.PID=f.PID "+
                           "where c.Nodename='一号食堂' " + strWhere + " group by e.MenuName,e.MenuId) K "
                         + "where K.rownumber > " + startrow.ToString() + " and K.rownumber < " + endrow.ToString();
            DataSet ds = db.ExecuteDataset(GetConntionString.conntionstring(), CommandType.Text, query);
            return ds;
        }

        public int GetListAllCount(string strWhere)
        {
            sqldb db = new sqldb();
            string query = string.Empty;
            query = "select COUNT(1) from (select e.MenuId,MenuName,COUNT(e.MenuName) as Counts,SUM(d.Price) as Price from Con_Record a " +
                    "inner join YAX_MACHINE b on a.MacID=b.MacID " +
                    "inner join Yax_Node c on b.NodeID=c.NodeID " +
                    "inner join CON_RECORD_DETAIL d on a.ID=d.Record_ID " +
                    "left join Con_Menus e on d.MenuId=e.MenuId " +
                    //"inner join FILE_PERSONNEL_VIEW f on a.PID=f.PID " +
                    "where c.Nodename='一号食堂' " + strWhere + " group by e.MenuName,e.MenuId)K";
            object obj = db.ExecuteScalar(GetConntionString.conntionstring(), CommandType.Text, query);
            return Convert.ToInt32(obj);
        }

        public DataSet GetListAll(string strWhere)
        {
            sqldb db = new sqldb();
            string query = string.Empty;
            query = "select MenuName,COUNT(e.MenuName) as Counts,SUM(d.Price) as Price,g.PeriodName from Con_Record a " +
                    "inner join YAX_MACHINE b on a.MacID=b.MacID " +
                    "inner join Yax_Node c on b.NodeID=c.NodeID " +
                    "inner join CON_RECORD_DETAIL d on a.ID=d.Record_ID " +
                    "left join Con_Menus e on d.MenuId=e.MenuId " +
                    //"inner join FILE_PERSONNEL_VIEW f on a.PID=f.PID " +
                    "left join Con_Period g on dbo.SP_GET_PERIODCODE_WC(a.RDateTime)=g.periodcode " +
                    "where c.Nodename='一号食堂' " + strWhere + " group by g.PeriodName,g.Periodcode,e.MenuName,e.menuid " +
                    "order by g.Periodcode,e.menuid ";
            DataSet ds = db.ExecuteDataset(GetConntionString.conntionstring(), CommandType.Text, query);
            return ds;
        }
        /// <summary>
        /// 获取每个餐次的菜的数量与金额合计
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public DataSet GetOneHJ(string strWhere)
        {
            sqldb db = new sqldb();
            string query = string.Empty;
            query = "select COUNT(e.MenuName) as Counts,SUM(d.Price) as Price,g.PeriodName from Con_Record a " +
                    "inner join YAX_MACHINE b on a.MacID=b.MacID " +
                    "inner join Yax_Node c on b.NodeID=c.NodeID " +
                    "inner join CON_RECORD_DETAIL d on a.ID=d.Record_ID " +
                    "left join Con_Menus e on d.MenuId=e.MenuId " +
                    //"inner join FILE_PERSONNEL_VIEW f on a.PID=f.PID " +
                    "left join Con_Period g on dbo.SP_GET_PERIODCODE_WC(a.RDateTime)=g.periodcode " +
                    "where c.Nodename='一号食堂' " + strWhere +
                    " group by g.PeriodName,g.Periodcode " +
                    "order by g.Periodcode ";
            DataSet ds = db.ExecuteDataset(GetConntionString.conntionstring(), CommandType.Text, query);
            return ds;
        }
        /// <summary>
        /// 获取总数量和金额合计
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public DataSet GetAllHJ(string strWhere)
        {
            sqldb db = new sqldb();
            string query = string.Empty;
            query = "select COUNT(e.MenuName) as Counts,SUM(d.Price) as Price from Con_Record a " +
                    "inner join YAX_MACHINE b on a.MacID=b.MacID " +
                    "inner join Yax_Node c on b.NodeID=c.NodeID " +
                    "inner join CON_RECORD_DETAIL d on a.ID=d.Record_ID " +
                    "left join Con_Menus e on d.MenuId=e.MenuId " +
                    //"inner join FILE_PERSONNEL_VIEW f on a.PID=f.PID " +
                    "left join Con_Period g on dbo.SP_GET_PERIODCODE_WC(a.RDateTime)=g.periodcode " +
                    "where c.Nodename='一号食堂' " + strWhere;
            DataSet ds = db.ExecuteDataset(GetConntionString.conntionstring(), CommandType.Text, query);
            return ds;
        }
    }
}
